﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[trgProductComponentInsertValidation]
   ON  [dbo].[tblProductComponent]
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare cProductComponentInsertValidation cursor for select ComponentProductID from inserted
	declare @ProductId int
	open cProductComponentInsertValidation
	fetch next from cProductComponentInsertValidation into @ProductID
	while @@fetch_status = 0
	begin

		declare @foo table ( productID int not null primary key )
		declare @ct int
		declare @lastCt int
		delete from @foo

		-- Get the chain of all component products for the products listed in inserted
		insert into @foo ( ProductID ) select ComponentProductID from tblProductComponent where ProductID = @ProductID
		set @ct = 0
		set @lastCt = -1
		while @ct <> @lastCt
		begin
			set @lastCt = @ct

			insert into @foo select distinct T2.ComponentProductID 
			from @foo T1 
				join tblProductComponent T2 on T1.ProductID = T2.ProductID
				left outer join @foo T3 on T2.ComponentProductID = T3.ProductID
			where T3.ProductID is null

			select @ct = count(*) from @foo
		end

		-- If any of the component products include the produts in inserted, then there is a loop
		if exists ( select 1 from @foo T1
						join inserted T2 on T1.ProductID = T2.ProductID )
		begin
			close cProductComponentInsertValidation
			deallocate cProductComponentInsertValidation
			rollback transaction
			RAISERROR ('Circular references are not allowed in the product component list.', 18,1)
			return
		end

	fetch next from cProductComponentInsertValidation into @ProductID
	end
	close cProductComponentInsertValidation
	deallocate cProductComponentInsertValidation

END
